﻿/*******************************************************************************
** Name:           DB Environmnet Builder.vbs
** Description:  
                    Script provides means for the environment build process:
                    1. Creates database environment from scratch.
                    2. Upgrades existing database from any intermediate build to
                       the latest and greatest using change log.
                    3. Updates database code (redeploys code which is marked
                       as redeployable, i.e. stored procedures, some UDFs, etc.)
                    4. Deletes database environment.
                    5. Creates full build (build from scratch) script(s)
                       from a database derived from a previoius build and then
                       upgraded incrementally to the lates and greatest one.
                    6. Creates database roles and grant permissions to these roles.
                    7. Creates SQL Server logins and maps them to the database 
                       users and roles.
'
'                   See detailed description of the actions performed by the script in 
'                   "DB Environment Builder Description.doc" document.
'
'** Input Parameters: 
'                   Script command line parameters:
'                       operation   delete|updatestructure|updatecode|delete|getfullbuildscripts|createroles|createusers
'                       envconfig    name of the file with environment configuration
'                       sqlservername   name of the target SQL Server instance 
'                       dbnamemapping   (optional) name of the file with database name mapping 
'                   If any


'** Returns:   Sets ERRORLEVEL environment variable to 0 in case of success 
'              or to a non-zero number in case of error.

'** Modification History
'** Sr  Date         Modified By               Reason
'**  1  05/28/2007   Alexander Karmanov        Initial version
'**  2  11/27/2008   Andriy Kvasnytsya         Proted to C#
 * '*******************************************************************************/

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;

namespace DbBuilder
{
	public static class Extention
	{
		public static string getAttribute(this XmlNode node, string key)
		{
			return node.Attributes[key] != null ? node.Attributes[key].Value : null;
		}

		public static StringBuilder StripEnd(this StringBuilder builder, int count)
		{
			builder.Remove(builder.Length - count, count);
			return builder;
		}
	}

	internal class Program
	{
		public enum Operation
		{
			Create, Delete, UpdateStructure, UpdateCode, GetDbScript, CreateRoles, CreateUsers,
			UpdateStaticData, Update
		}

		//Script configuration
		public string script_execution_folder;
		public string ENVIRONMENT_CONFIG_FILE_NAME; // Enviromnet config file fully qualified name

		public string config_root_folder;
		//A folder where the configuration files is located - all other configuration files are relative to that folder

		// Environment configuration 
		private XmlDocument environmentConfigXML;
		public DatabaseEnvironment DatabaseEnvironment;
		public string SQLServerInstanceName;
		public Dictionary<string, string> databaseNameMapping;
		private string DataFolderName;

		private readonly static Program _instance = new Program();
		public static Program Instance
		{
			get { return _instance; }
		}

		public static void Main(string[] args)
		{
			_instance._Main(args);
		}

		private void _Main(string[] args)
		{
			// Initialize log file
			script_execution_folder = Directory.GetCurrentDirectory();
			Logger.InitLog();
			// Get what the script should do (do it before the configuration files check, but perform the action later)
			Operation operationToPerform = DetectRequestedOperation(args);
			// Load the configuration
			LoadEnvironmentConfig(args);
			// Load/compose database name mapping
			LoadDatabaseNameMapping(args);
			// Get the target server name
			SQLServerInstanceName = GetSQLServerInstanceName(args);
			// Get default data folder/log folder
			GetDataAndLogFolders();
			// Perform requested action
			switch (operationToPerform)
			{
			case Operation.Create:
				CreateEnvironment();
				break;
			case Operation.UpdateStructure:
				UpdateEnvironment();
				break;
			case Operation.UpdateCode:
				RedeployCode();
				break;
			case Operation.UpdateStaticData:
				RedeployStaticData();
				break;
			case Operation.Delete:
				DeleteEnvironment();
				break;
			case Operation.GetDbScript:
				ScriptDatabaseObjects();
				break;
			case Operation.CreateRoles:
				DefineRolesAndPermissions();
				break;
			case Operation.CreateUsers:
				DefineLoginsAndUsers();
				break;
			case Operation.Update:
				UpdateEnvironment();
				RedeployCode();
				RedeployStaticData();
				break;
			default:
				throw new InvalidOperationException();
			}
		}

		private void RedeployStaticData()
		{
			foreach (Database database in DatabaseEnvironment.Databases)
			{
				foreach (ConfigurationFile file in database.StaticData)
				{
					new ScriptList(file.FileName).Execute(true);
				}
			}
		}

		private void UpdateEnvironment()
		{
			Logger.WriteTrace
				("Script is going to update database environment on server " + SQLServerInstanceName +
					" according to given configuration file");

			//Load change log file
			string ChangeLogFileName;
			ChangeLog ChangeLogXML;
			string ChangeLogFolder;
			InitChangeLog(out ChangeLogFileName, out ChangeLogXML, out ChangeLogFolder);

			//Get enviroment build number
			string EnvironmentBuildNumber = GetEnvironmentBuildVersion();
			if (string.IsNullOrEmpty(EnvironmentBuildNumber))
			{
				Logger.WriteTrace("Script could not identify environment build number. The script is aborted.");
				Environment.Exit(1);
			}

			string ChangeLogLatestBuildNumber = ChangeLogXML.LatestBuildNumber;

			if (!ChangeLogXML.Keys.Contains(EnvironmentBuildNumber))
			{
				Logger.WriteTrace
					(string.Format("Script could not environment build number: '{0}' in the change log. No point to start. The script is aborted.", EnvironmentBuildNumber));
				Environment.Exit(1);
			}

			if (EnvironmentBuildNumber == ChangeLogLatestBuildNumber)
			{
				Logger.WriteTrace
					(string.Format("Script finishes without any action since the existing environment build is the same as the one being deployed: '{0}'", EnvironmentBuildNumber));
				return;
			}

			foreach (KeyValuePair<string, ScriptList> version in ChangeLogXML)
			{
				if (string.Compare(version.Key, EnvironmentBuildNumber) > 0)
				{
					if(!version.Value.Execute(false))
					{
						Logger.WriteTrace("Script failed to execute script");
						Environment.Exit(1);
					}
					else
					{
						// build number for all databases in the environment
						SetEnvironmentBuildNumber(version.Key);
					}
				}
			}

			Logger.WriteTrace
				(string.Format("Environment update finished, the database environment was upgraded to build '{0}'.", ChangeLogLatestBuildNumber));
		}

		private void SetEnvironmentBuildNumber(string buildNumber)
		{
			Logger.WriteTrace("Setting build number " + buildNumber + " to all databases in the environment");

			foreach (string dbName in databaseNameMapping.Values)
			{
				setBuildNumber(dbName, buildNumber);
				Logger.WriteTrace("Database " + dbName + " - ok");
			}

			Logger.WriteTrace("Environment databases build number to: " + buildNumber);
		}



		private void InitChangeLog(out string ChangeLogFileName, out ChangeLog ChangeLogXml, out string ChangeLogFolder)
		{
			Logger.WriteTrace("Trying to locate and load change log file");

			ChangeLogFileName = config_root_folder + "\\" +
				environmentConfigXML.SelectSingleNode("//System/SchemaChangeLog").getAttribute("FileName");
			Logger.WriteTrace("Trying to locate change log file: " + ChangeLogFileName);
			if (!File.Exists(ChangeLogFileName))
			{
				Logger.WriteTrace("ERROR: Change log file " + ChangeLogFileName + " could not be found.");
				Logger.WriteTrace("ERROR: Script execution is interrupted.");
				Environment.Exit(1);
			}

			Logger.WriteTrace("Loading change log file: " + ChangeLogFileName);
			//Reading an XML file with change log
			ChangeLogXml = new ChangeLog(ChangeLogFileName);
			Logger.WriteTrace("Change log file file loaded:" + ChangeLogFileName);

			ChangeLogFolder = Path.GetDirectoryName(ChangeLogFileName);
			Logger.WriteTrace("Change log files are located in " + ChangeLogFolder);
		}

		private void ScriptDatabaseObjects()
		{
			Logger.WriteTrace
				("Script is going to script database schema object from server " + SQLServerInstanceName +
					" for database(s) listed in the environment configuration file '" + ENVIRONMENT_CONFIG_FILE_NAME + "'");

			//Get enviroment build number - as a side effect check existence of all the databases mentioned in the config file.
			string EnvironmentBuildNumber = GetEnvironmentBuildVersion();
			if (EnvironmentBuildNumber == "")
			{
				Logger.WriteTrace("Script could not identify environment build number. The script is aborted.");
				Environment.Exit(1);
			}

			//get build number from the configuration file
			var ConfigurationBuildNumber = environmentConfigXML.SelectSingleNode("//System/Build").getAttribute("Number");
			Logger.WriteTrace
				("Environment build number is '" + EnvironmentBuildNumber + "' and the configuration build number is '" +
					ConfigurationBuildNumber + "'");

			//Get configuration file listing the scripts defined for each database in environment

			Logger.WriteTrace("Traversing list of databases and getting object creation script for each of them");
			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				string dbOriginalName = dbNode.getAttribute("Name");
				string dbMappedName = getMappedName(dbOriginalName);
				string ObjectCreationScriptFileName = config_root_folder + "\\" +
					dbNode.SelectSingleNode("FullBuildObjectCreation").getAttribute("ConfigurationFileName");
				Logger.WriteTrace
					("Getting objects creation script from database " + dbOriginalName + " (mapped to " + dbMappedName +
						") into file '" + ObjectCreationScriptFileName + "'");
				// extract script
				ScriptObjectsInADatabase(dbMappedName, ObjectCreationScriptFileName);
			}

			//Write the environment build number into the configuration file - that would correspond the files we just have created
			Logger.WriteTrace
				("Changing build number in environment configuration file from " + ConfigurationBuildNumber + " to " +
					EnvironmentBuildNumber);
			// Creating objects for filesystem operations (
			// Preserve the existing file
			if (File.Exists(ENVIRONMENT_CONFIG_FILE_NAME))
			{
				string fileNameBak = ENVIRONMENT_CONFIG_FILE_NAME + ".bak";
				Logger.WriteTrace
					("Preserving existing environment configuration file " + ENVIRONMENT_CONFIG_FILE_NAME + "\n" + " as " + fileNameBak);
				File.Copy(ENVIRONMENT_CONFIG_FILE_NAME, fileNameBak);
				Logger.WriteTrace("Existing environment configuration file is saved with name " + fileNameBak);
			}
			// Update the xml and save it back to file
			var buildNumberNode = environmentConfigXML.SelectSingleNode("//System/Build");
			buildNumberNode.Attributes["Number"].Value = EnvironmentBuildNumber;
			environmentConfigXML.Save(ENVIRONMENT_CONFIG_FILE_NAME);

			Logger.WriteTrace
				("Script execution completed: Database object creation script(s) is extracted, build number in the configuration file is changed to " +
					EnvironmentBuildNumber);
		}

		private static void ScriptObjectsInADatabase(string dbName, string fileName)
		{
			Logger.WriteTrace("Scripting objects in database " + dbName + " using config file '" + fileName + "'");

			Logger.WriteTrace("Getting all the scripts from config file in a single list");
			ScriptList ScriptList = new ScriptList(fileName);
			ScriptList.Execute(false);
		}

		private void RedeployCode()
		{
			Logger.WriteTrace
				("Script is going to re-deploy code (UDFs, SPs) intentionally marked for that in configuration files on server " +
					SQLServerInstanceName);

			//Get enviroment build number - as a side effect check existence of all the databases mentioned in the config file.
			string EnvironmentBuildNumber = GetEnvironmentBuildVersion();
			if (string.IsNullOrEmpty(EnvironmentBuildNumber))
			{
				Logger.WriteTrace("Script could not identify environment build number. The script is aborted.");
				Environment.Exit(1);
			}

			//get build number from the configuration file
			string ConfigurationBuildNumber = environmentConfigXML.SelectSingleNode("//System/Build").getAttribute("Number");

			//check if the build number in the configuration file matches the one in the database - stop execution if they are different
			if (EnvironmentBuildNumber != ConfigurationBuildNumber)
			{
				Logger.WriteTrace
					("Environment build (" + EnvironmentBuildNumber +
						") does not match build number specified in the configuration file (" + ConfigurationBuildNumber +
							"). Script is aborted.");
				Environment.Exit(1);
			}
			else
			{
				Logger.WriteTrace
					("Environment build (" + EnvironmentBuildNumber + ") matches number specified in the configuration file (" +
						ConfigurationBuildNumber + ").");
			}

			//Redeploying code for databases from the list
			Logger.WriteTrace("Updating code in databases as per environment configuration file");

			RedeployDatabasesCode();

			Logger.WriteTrace("Script execution completed: code is re-deployed.");
		}

		private string GetEnvironmentBuildVersion()
		{
			// to keep build numbers temporarily - to detect differences
			string LastBuildNumber = ""; //initialize variable
			bool DifferentBuildNumbersFound = false;

			// scroll through the mapped names 
			foreach (string dbName in databaseNameMapping.Values)
			{
				//dbOriginalName = databaseNameMapping.Keys(i)
				//dbMappedName = databaseNameMapping.Items(i)

				string DatabaseBuildNumber = GetDBBuildNumber(dbName);
				Logger.WriteTrace("Database " + dbName + " has build number: '" + DatabaseBuildNumber + "'");
				if ((LastBuildNumber != "") && (DatabaseBuildNumber != "") && (DatabaseBuildNumber != LastBuildNumber))
				{
					DifferentBuildNumbersFound = true;
				}
				if (DatabaseBuildNumber != "")
				{
					LastBuildNumber = DatabaseBuildNumber;
				}
			}

			if (!DifferentBuildNumbersFound)
			{
				Logger.WriteTrace("Environment is detected as build: '" + LastBuildNumber + "'");
				return LastBuildNumber;
			}
			else
			{
				Logger.WriteTrace
					("ERROR: Found difference in database build numbers while all the databases within environment are supposed to have the same build number.");
				return string.Empty;
			}
		}

		private string GetDBBuildNumber(string dbName)
		{
			using (SqlConnection objConnection = new SqlConnection
					(string.Format("Server={0};Database={1};Integrated Security=SSPI;", SQLServerInstanceName, dbName)))
			{
				objConnection.Open();

				const string sql =
					"select cast(value as varchar(20)) as build_number from sys.fn_listextendedproperty ('databasebuildnumber', null, null, null, null, null, null)";

				SqlCommand command = new SqlCommand(sql, objConnection);

				object result = command.ExecuteScalar();
				return !(result is DBNull) ? (string) result : null;
			}
		}

		private void RedeployDatabasesCode()
		{
			Logger.WriteTrace("Preparing to re-deploy  code in following databases as defined in configuration file:");
			string dbMappedName, dbOriginalName;
			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				dbOriginalName = dbNode.getAttribute("Name");
				dbMappedName = getMappedName(dbOriginalName);
				Logger.WriteTrace(dbOriginalName + " -- (name mapped to) --> " + dbMappedName);
			}

			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				dbOriginalName = dbNode.getAttribute("Name");
				dbMappedName = getMappedName(dbOriginalName);
				string ObjectDataCreationScriptFileName = config_root_folder + "\\" +
					dbNode.SelectSingleNode("ObjectDataCreationScript").getAttribute("ConfigurationFileName");
				Logger.WriteTrace("Re-deploying code objects in database " + dbOriginalName + "(" + dbMappedName + ")");
				// populate database with objects and the data
				if (!CreateDatabaseObjects(dbMappedName, ObjectDataCreationScriptFileName, true))
				{
					Logger.WriteTrace
						("Failed to create objects/data in database " + dbOriginalName + " -- (name mapped to) --> " + dbMappedName);
					Logger.WriteTrace("Script execution is aborted.");
					Environment.Exit(1);
				}
			}
		}

		private void DeleteEnvironment()
		{
			Logger.WriteTrace
				("Script is going to delete from server " + SQLServerInstanceName +
					" database(s) enlisted in given environment configuration file (according to database name mapping, if any)");

			DeleteDatabases();
		}

		private void DeleteDatabases()
		{
			Logger.WriteTrace("Preparing to delete following databases as defined in configuration file:");
			string dbMappedName, dbOriginalName;
			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				dbOriginalName = dbNode.Attributes["Name"].Value;
				dbMappedName = databaseNameMapping[dbOriginalName];
				Logger.WriteTrace(dbOriginalName + " -- (name mapped to) --> " + dbMappedName);
			}

			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				dbOriginalName = dbNode.Attributes["Name"].Value;
				dbMappedName = databaseNameMapping[dbOriginalName];
				DeleteDatabase(dbMappedName);
			}

			Logger.WriteTrace("Script execution completed: database environment is deleted.");
		}

		private void DeleteDatabase(string dbName)
		{
			string sql = string.Format
				(@"ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE {0} SET SINGLE_USER
GO
DROP DATABASE {0}
GO",
					dbName);

			Logger.WriteTrace("Following statement is prepared to drop database " + dbName + ":");
			Logger.WriteTrace(sql);
			Logger.WriteTrace("=======================================end of script==========================");

			// Execute the script 
			RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sql);
		}

		private void CreateEnvironment()
		{
			Logger.WriteTrace
				("Script is going to create on server " + SQLServerInstanceName +
					" a new database environment as per given configuration file");

			//Check existence of all the databases from the configuratin file - if any of them exists - quit reporting it.
			if (ADatabaseFromTheListExists())
			{
				Logger.WriteTrace
					("One or more databases configured in the environment configuration file " + ENVIRONMENT_CONFIG_FILE_NAME +
						" already exists. Script is aborted.");
				Environment.Exit(1);
			}
			else
			{
				Logger.WriteTrace("No databases that already exist found - the script continues");
			}

			//Create databases from the list
			Logger.WriteTrace("Creating databases as per environment configuration file" + ENVIRONMENT_CONFIG_FILE_NAME);
			CreateDatabases();
			Logger.WriteTrace
				("Defining roles and granting permissions per environment configuration file" + ENVIRONMENT_CONFIG_FILE_NAME);
			DefineRolesAndPermissions();
			Logger.WriteTrace
				("Creating logins and mapping them to database users as per environment configuration file" +
					ENVIRONMENT_CONFIG_FILE_NAME);
			DefineLoginsAndUsers();
			Logger.WriteTrace("Script execution completed: database environment is created.");
		}

		private void DefineLoginsAndUsers()
		{
			Logger.WriteTrace
				("Script is going to create logins and map them to database users in server " + SQLServerInstanceName +
					" for database(s) listed in the environment configuration file '" + ENVIRONMENT_CONFIG_FILE_NAME + "'");

			//Get configuration file with logins for the environment
			string LoginsUsersConfigFileName = config_root_folder + "\\" +
				environmentConfigXML.SelectSingleNode("//System/Security/LoginsAndUsers").getAttribute("ConfigurationFileName");
			Logger.WriteTrace
				("Loading configuration file with login and database user information: '" + LoginsUsersConfigFileName + "'");
			XmlDocument ConfigXML = new XmlDocument();
			// try to load that string into XML document
			ConfigXML.Load(LoginsUsersConfigFileName);
			Logger.WriteTrace("Configuration file loaded ok:" + LoginsUsersConfigFileName);
			Logger.WriteTrace(ConfigXML.OuterXml);

			// variables for the login list traversing 
			string loginName;

			//Check existence of all logins listed in the environment configuration file -  create the if needed
			Logger.WriteTrace("Traversing list of logins and create them (if they do not exist) as defined in the config file");
			foreach (XmlNode loginNode in ConfigXML.SelectNodes("//Logins/Login"))
			{
				string loginType = loginNode.getAttribute("Type");
				loginName = loginNode.getAttribute("Name");
				string loginPwd = loginNode.getAttribute("Password");
				Logger.WriteTrace("Checking/creating login '" + loginName + "' on server " + SQLServerInstanceName);
				// Check existence/create login
				CheckOrRecreateLogin(loginType, loginName, loginPwd);
			}

			//Revoke access for the logins to the databases in the target environment
			Logger.WriteTrace("Traversing list of logins and revoke their access to all databases in the enviroment");
			foreach (XmlNode loginNode in ConfigXML.SelectNodes("//Logins/Login"))
			{
				loginName = loginNode.getAttribute("Name");
				Logger.WriteTrace("Revoking access to the databases for login '" + loginName + "'");
				// Check existence/create login
				RevokeAccessToDatabases(loginName);
			}

			// Map each login to database user in each database it's specifed for
			Logger.WriteTrace("Traversing list of logins and map them to the database users as specified in config file");
			foreach (XmlNode loginNode in ConfigXML.SelectNodes("//Logins/Login"))
			{
				loginName = loginNode.getAttribute("Name");
				foreach (XmlNode dbNode in loginNode.SelectNodes("Database"))
				{
					string dbName = dbNode.getAttribute("Name");
					string dbMappedName = getMappedName(dbName);
					string dbUserName = dbNode.getAttribute("User");
					Logger.WriteTrace
						("Mapping login " + loginName + " to a user " + dbUserName + " in database " + dbName + " (mapped to " +
							dbMappedName + ")");
					MapDatabaseUser(dbMappedName, loginName, dbUserName);
					foreach (XmlNode roleNode in dbNode.SelectNodes("Role"))
					{
						string roleName = roleNode.getAttribute("Name");

						Logger.WriteTrace
							("Adding user " + dbUserName + " to a db role " + roleName + " in database " + dbName + " (mapped to " +
								dbMappedName + ")");
						MapUserToRole(dbMappedName, dbUserName, roleName);
					}
				}
			}

			Logger.WriteTrace("Script execution completed: Server logins are created/mapped to the database users.");
		}

		private void MapUserToRole(string DatabaseName, string UserName, string RoleName)
		{
			Logger.WriteTrace(string.Format("Mapping user '{0}' to '{1}' role in '{2}' database", UserName, RoleName, DatabaseName));
			string sqlText = string.Format
				("use {0}; exec dbo.sp_addrolemember @rolename = '{1}', @membername = '{2}'", DatabaseName, RoleName, UserName);

			RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sqlText);

			Logger.WriteTrace
				(string.Format
					("Database user '{0}' must be mapped to role '{1}' in '{2}' database", UserName, RoleName, DatabaseName));
		}

		private void MapDatabaseUser(string DatabaseName, string LoginName, string UserName)
		{
			Logger.WriteTrace(string.Format("Mapping login {0} to a user '{1}' in database '{2}'", LoginName, UserName, DatabaseName));

			Logger.WriteTrace(string.Format("Dropping existing user {0} in database {1} (if any)", UserName, DatabaseName));
			string sqlText = string.Format
				("use {0}; if exists(select * from sysusers where name = '{1}') exec dbo.sp_dropuser '{2}';", DatabaseName, UserName,
					UserName);
			RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sqlText);

			Logger.WriteTrace
				(string.Format
					("Granting for login '{0}' access to database '{1}' as  user '{2}'", LoginName, DatabaseName, UserName));
			sqlText = string.Format
				("use {0}; exec dbo.sp_grantdbaccess @loginame = '{1}', @name_in_db = '{2}'", DatabaseName, LoginName, UserName);

			RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sqlText);
			Logger.WriteTrace
				(string.Format("Login '{0}' is allowed to access '{1}' database as user '{2}'", LoginName, DatabaseName, UserName));
		}

		private void RevokeAccessToDatabases(string LoginName)
		{
			string dbNameList = string.Empty;
			foreach (var  dbName in databaseNameMapping)
			{
				if (dbNameList.Length != 0)
				{
					dbNameList += ", ";
				}
				dbNameList = dbNameList + "'" + dbName + "'";
			}

			Logger.WriteTrace("Revoking access for login " + LoginName + " to databases: " + dbNameList);
			String sqlText = string.Format
				(@"nocount on 
declare 
  @sql varchar(8000), 
  @c char(2) 
select 
  @sql = null,
  @c = char(13) + char(10) 
if object_id('tempdb..#t') is not null drop table #t 
create table #t(dbname sysname, username sysname) 
select 
  @sql = isnull(@sql + ' union all' + @c, '') + @c + 
        'select ''' + d.name + ''', u.name ' + @c + 
        ' from master..syslogins l ' + @c + 
        '   join ' + d.name + '..sysusers u on l.sid = u.sid' + @c + 
        ' where l.name = ''' + '{0} ' + '''' + @c 
  from master..sysdatabases d 
  where d.name in ({1})
insert into #t 
exec(@sql) 
select 
  @sql = isnull(@sql + @c, '') + 
        'use ' + t.dbname + '; exec sp_revokedbaccess ''' + t.username + '''' 
  from #t t 
exec(@sql)",
					LoginName, dbNameList);

			RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sqlText);
			Logger.WriteTrace("Revoked succesfully access for login " + LoginName + " to databases: " + dbNameList);
		}

		private void CheckOrRecreateLogin(string loginType, string LoginName, string Password)
		{
			string sqlText;

			// depending on the type of the login either create it or grant login permission
			if (loginType.ToLower() == "sql")
			{
				Logger.WriteTrace("Creating SQL Server login '" + LoginName + "' with password provided");
				sqlText = string.Format
					(@"
if not exists(select * from syslogins where name = '{0}' 
  exec sp_addlogin @loginame = '{0}', @passwd = '{1}'",
						LoginName, Password);
			}
			else if (loginType.ToLower() == "windows")
			{
				Logger.WriteTrace("Granting login privileges to Windows login '" + LoginName + "'");
				sqlText = string.Format
					(@"
if not exists(select * from syslogins where name = '{0}')
  exec sp_grantlogin @loginame = '{1}'", LoginName,
						LoginName);
			}
			else
			{
				Logger.WriteTrace("Wrong login type '" + loginType + "' was specified for login '" + LoginName + "'");
				return;
			}

			RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sqlText);
			Logger.WriteTrace("Login '" + LoginName + "' created/exists in the server.");
		}

		private void DefineRolesAndPermissions()
		{
			Logger.WriteTrace
				("Script is going to create database-level roles and grant permissinons in server " + SQLServerInstanceName +
					" for database(s) listed in the environment configuration file '" + ENVIRONMENT_CONFIG_FILE_NAME + "'");

			//Get configuration files defining roles and permissions for each database in the environment
			Logger.WriteTrace
				("Traversing list of databases and create roles/grant permissions as per script mentioned in RolesAndObjectLevelPermissions element");
			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				string dbOriginalName = dbNode.getAttribute("Name");
				string dbMappedName = getMappedName(dbOriginalName);
				XmlNode configFileNode = dbNode.SelectSingleNode("RolesAndObjectLevelPermissions");
				if (configFileNode != null)
				{
					string RolesPermissionsConfigFileName = config_root_folder + "\\" +
						configFileNode.getAttribute("ConfigurationFileName");
					Logger.WriteTrace
						("Database " + dbOriginalName + " (mapped to " + dbMappedName +
							"):creating roles/granting permissions as per file '" + RolesPermissionsConfigFileName + "'");
					// Create Roles/Grant permissions
					if (!DefineRolesAndPermissionsInADatabase(dbMappedName, RolesPermissionsConfigFileName))
					{
						Logger.WriteTrace
							("Failed to create roles and permissions in database " + dbOriginalName + " (mapped to " + dbMappedName + ")");
						Logger.WriteTrace("Script execution is aborted.");
						Environment.Exit(1);
					}
					Logger.WriteTrace
						("Created all roles/granted all permissions configured for database " + dbOriginalName + " (mapped to " +
							dbMappedName + ") in file '" + RolesPermissionsConfigFileName + "'");
				}
				else
				{
					Logger.WriteTrace
						("There is no role/permission configuration file specified for database " + dbOriginalName + " (mapped to " +
							dbMappedName + ")");
				}
			}

			Logger.WriteTrace("Script execution completed: Database-level roles were created and permissions were granted");
		}

		private bool DefineRolesAndPermissionsInADatabase(string dbName, string fileName)
		{
			Logger.WriteTrace("Creating roles/granting permissions in database " + dbName + " using config file '" + fileName + "'");

			Logger.WriteTrace("Loading configuration file...");

			XmlDocument ConfigXML = new XmlDocument();
			// try to load that string into XML document
			ConfigXML.Load(fileName);
			Logger.WriteTrace("Configuration file loaded ok:" + fileName);
			Logger.WriteTrace(ConfigXML.OuterXml);

			foreach (XmlNode roleNode in ConfigXML.SelectNodes("//Roles/Role"))
			{
				string roleName = roleNode.getAttribute("Name");

				CreateRoleInADatabase(dbName, roleName);

				RevokePermissionsFromRole(dbName, roleName);

				foreach (XmlNode objectNode in roleNode.SelectNodes("//Object"))
				{
					string objectName = objectNode.getAttribute("Name");
					string objectPermission = objectNode.getAttribute("Permission");
					GrantPermission(dbName, roleName, objectName, objectPermission);
				}
			}
			return true;
		}

		private void GrantPermission(string DatabaseName, string RoleName, string ObjectName, string PermissionName)
		{
			Logger.WriteTrace
				("Granting role '" + RoleName + "' in '" + DatabaseName + "' database '" + PermissionName + "' permission on '" +
					ObjectName + "'");

			string sqlText = string.Format("grant {0} on {1} to {2}", PermissionName, ObjectName, RoleName);

			RunSQLCommandWithOSQL(SQLServerInstanceName, DatabaseName, sqlText);
		}

		private void RevokePermissionsFromRole(string DatabaseName, string RoleName)
		{
			Logger.WriteTrace
				(string.Format("Revoking all direct permissions from '{0}' role in '{1}' database", RoleName, DatabaseName));
			string sqlText = string.Format
				(@"
declare
  @role_name sysname,
  @rc int;
select @role_name = '{0}';
declare objects cursor static for
  select name, schema_name(uid)
    from dbo.sysobjects o with (nolock)
      join dbo.syspermissions p with (nolock)
        on o.id = p.id
    where p.grantee = user_id(@role_name)
    order by o.name;
open objects;
declare
  @obj_name sysname,
  @obj_schema sysname,
  @sql varchar(2000);
fetch next from objects into @obj_name, @obj_schema;
while @@fetch_status = 0   
  begin
    select @sql = 'revoke all on [' + @obj_schema + '].[' + @obj_name + '] from ' + @role_name;
    exec(@sql);
    if @@error != 0
      print 'FAILURE: ' + @sql;
    else
      print 'success: ' + @sql;
    fetch next from objects into @obj_name, @obj_schema;
  end
close objects;
deallocate objects;",
					RoleName);
			RunSQLCommandWithOSQL(SQLServerInstanceName, DatabaseName, sqlText);
			Logger.WriteTrace
				("Role '" + RoleName + "' should not have any directly granted permission by now in database '" + DatabaseName + "'");
		}

		private void CreateRoleInADatabase(string DatabaseName, string RoleName)
		{
			Logger.WriteTrace
				("Checking existence of role '" + RoleName + "' in '" + DatabaseName + "' database, creating it if does not exist.");
			string sqlText = string.Format
				(@"print 'Current DB is: ' + db_name() 
declare  
  @role_name sysname, 
  @rc int; 
select @role_name = '{0}'; 
 
if not exists (select * from dbo.sysusers where name = @role_name and issqlrole = 1) 
  begin 
  exec @rc = sp_addrole @role_name;
    if @rc = 0 
      print 'Role ''' + @role_name + ''' was created in the database'; 
    else  
      begin 
        print 'FAILURE: failed to create role ''' +  @role_name + ''''; 
        return; 
      end 
  end 
else
  print 'Role ''' + @role_name + ''' already exists in the database ''' + db_name() + ''' - proceeding with that'; ",
					RoleName);
			RunSQLCommandWithOSQL(SQLServerInstanceName, DatabaseName, sqlText);
			Logger.WriteTrace("Role '" + RoleName + "' exists in database '" + DatabaseName + "' now.");
		}

		private string getMappedName(string name)
		{
			return databaseNameMapping[name];
		}

		private void CreateDatabases()
		{
			Logger.WriteTrace("Preparing to create following databases as defined in configuration file:");
			string dbMappedName, dbOriginalName;
			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				dbOriginalName = dbNode.Attributes["Name"].Value;
				dbMappedName = databaseNameMapping[dbOriginalName];
				Logger.WriteTrace(dbOriginalName + " -- (name mapped to) --> " + dbMappedName);
			}

			//get build number from the configuration file
			string buildNumber = environmentConfigXML.SelectSingleNode("//System/Build").Attributes["Number"].Value;

			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				dbOriginalName = dbNode.Attributes["Name"].Value;
				dbMappedName = databaseNameMapping[dbOriginalName];
				string DatabaseLayoutFileName = config_root_folder + "\\" +
					dbNode.SelectSingleNode("FileLayout").Attributes["ConfigurationFileName"].Value;
				string ObjectDataCreationScriptFileName = config_root_folder + "\\" +
					dbNode.SelectSingleNode("ObjectDataCreationScript").Attributes["ConfigurationFileName"].Value;
				// create empty database
				if (!CreateDatabase(dbMappedName, DatabaseLayoutFileName))
				{
					Logger.WriteTrace("Failed to create database " + dbOriginalName + " -- (name mapped to) --> " + dbMappedName);
					Logger.WriteTrace("Script execution is aborted.");
					Environment.Exit(1);
				}

				// populate database with objects and the data
				if (!CreateDatabaseObjects(dbMappedName, ObjectDataCreationScriptFileName, false))
				{
					Logger.WriteTrace
						("Failed to create objects/data in database " + dbOriginalName + " -- (name mapped to) --> " + dbMappedName);
					Logger.WriteTrace("Script execution is aborted.");
					Environment.Exit(1);
				}
				// specify build number for each database
				setBuildNumber(dbMappedName, buildNumber);
			}
		}

		private void setBuildNumber(string dbName, string buildNumber)
		{
			Logger.WriteTrace("Labelling database " + dbName + "with build number '" + buildNumber + "'");

			//compose a sql command and execute it against target database.
			//the code was borrowed from a stored procedures that used to be used before
			string sql = string.Format
				(@" declare @buildnumber varchar(20)
 select @buildnumber = '{0}'
 
  declare  
    @currentbuild varchar(20) 
  select @currentbuild = null 
 
  -- check the existence of the extended property 
  select @currentbuild = cast(value as varchar(20)) 
    from sys.fn_listextendedproperty ('databasebuildnumber', null, null, null, null, null, null) 
 
  -- if there is no such property - create it and set to proper value 
  if @currentbuild is null 
    begin 
      print 'There is no database build number found in the database ''' + db_name() + ''' - a new number will be written down.' 
      exec sys.sp_addextendedproperty  
        @name = N'databasebuildnumber',  
        @value = @buildnumber 
    end 
  else  
    begin 
      print 'Database ''' + db_name() + ''' build number is: ''' + isnull(@currentbuild, 'NULL') + '''' 
      exec sys.sp_updateextendedproperty  
        @name = N'databasebuildnumber',  
        @value = @buildnumber 
    end 
  -- check and report what was written to the extended property 
  select @buildnumber = null 
  select @buildnumber = cast(value as varchar(20)) 
    from sys.fn_listextendedproperty ('databasebuildnumber', null, null, null, null, null, null) 
  print 'Database ''' + db_name() + ''' build number is set to: ''' + isnull(@buildnumber, 'NULL') + '''' 
",
					buildNumber);

			RunSQLCommandWithOSQL(SQLServerInstanceName, dbName, sql);
		}

		private static bool CreateDatabaseObjects(string dbName, string fileName, bool redeployableOnly)
		{
			Logger.WriteTrace("Starting object creation in database " + dbName + " as defined in file " + fileName);

			ScriptList ScriptList = new ScriptList(fileName);

			if (!ScriptList.Execute(redeployableOnly))
			{
				Logger.WriteTrace("Failed to create objects in database " + dbName + " as defined in file " + fileName);
				return false;
			}

			Logger.WriteTrace ("Completed object creation in database " + dbName + " as defined in file " + fileName);
			return true;
		}

		private bool CreateDatabase(string dbName, string DatabaseLayoutFileName)
		{
			Logger.WriteTrace("Going to create database " + dbName + " using file layout descibed in " + DatabaseLayoutFileName);

			// All the files have a name relative to the database configuration file - so there is a need to detect its location:

			Logger.WriteTrace("Trying to locate configuration file: " + DatabaseLayoutFileName);
			if (!File.Exists(DatabaseLayoutFileName))
			{
				Logger.WriteTrace("ERROR: Configuration file " + DatabaseLayoutFileName + " could not be found/open.");
				Logger.WriteTrace("ERROR: Script execution is aborted.");
				return false;
			}

			//get the config to avoid getting it over and over    
			XmlDocument xmlDatabaseLayout = new XmlDocument();

			Logger.WriteTrace("Loading configuration file: " + DatabaseLayoutFileName);
			//Reading an XML file with configuration
			xmlDatabaseLayout.Load(DatabaseLayoutFileName);
			Logger.WriteTrace("Configuration file loaded:" + DatabaseLayoutFileName);

			//Get an environment/configuration node for the current server and pass it down 
			//to a filegroup/log composing function to avoid doing it many-many times.
			Logger.WriteTrace
				("Searching for an environment configuration that would include SQL Server instance '" + SQLServerInstanceName + "'");
			XmlNode xmlEnvironment = null;
			foreach (XmlNode envNode in xmlDatabaseLayout.SelectNodes("//Database/Environment"))
			{
				Logger.WriteTrace("Searching in environment configuration named '" + envNode.Attributes["Name"].Value + "'");
				foreach (XmlNode instNode in envNode.SelectNodes("SQLServerInstances/SQLServerInstance"))
				{
					Logger.WriteTrace("There is a SQL Server instance named '" + instNode.Attributes["Name"].Value + "'");
					if (instNode.Attributes["Name"].Value.ToUpper() == SQLServerInstanceName.ToUpper())
					{
						xmlEnvironment = envNode;
						Logger.WriteTrace
							("Found environment configuration named '" + envNode.Attributes["Name"].Value +
								"' that includes definition of log files for SQL Server instance '" + SQLServerInstanceName + "'");
						break;
					}
				}
				if (xmlEnvironment != null)
				{
					break;
				}
			}

			if (xmlEnvironment == null)
			{
				Logger.WriteTrace
					("Could not find environment configuration that would include SQL Server instance '" + SQLServerInstanceName + "'.");
				Logger.WriteTrace
					("Database " + dbName +
						" data/log files will be created in default location with default size/growth and named after database/filegroup.");
			}

			var sql = string.Format
				("create database {0} ON {1} --LOG ON {2}", dbName,
					getFilegroupsDefinition(dbName, xmlDatabaseLayout, xmlEnvironment), getLogFilesDefinition(dbName, xmlEnvironment));

			Logger.WriteTrace("Following statement is prepared to create database " + dbName + ":");
			Logger.WriteTrace(sql);
			Logger.WriteTrace("=======================================end of script==========================");

			RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sql);

			SetDatabaseOptions(dbName, xmlDatabaseLayout);
			return true;
		}

		private void SetDatabaseOptions(string dbName, XmlDocument xmlFileLayout)
		{
			Logger.WriteTrace("Setting database options for database " + dbName + " as specified in the database definition file.");
			//go through the list of options and apply each of them
			foreach (XmlNode optNode in xmlFileLayout.SelectNodes("//Database/DatabaseOptions/Option"))
			{
				string optionName = optNode.Attributes["Name"].Value;
				string optionValue = optNode.Attributes["Value"].Value;
				string optionCondition = optNode.Attributes["Condition"].Value;
				string sql = "exec sp_dboption '" + dbName + "', '" + optionName + "', '" + optionValue + "'";
				if (!string.IsNullOrEmpty(optionCondition))
				{
					sql = string.Format("if {0}\n  {1}", optionCondition, sql);
				}

				Logger.WriteTrace("Setting option '" + optionName + "' to '" + optionValue + "'");
				RunSQLCommandWithOSQL(SQLServerInstanceName, "master", sql);
			}
		}

		private static void RunSQLCommandWithOSQL(string server, string dbName, string sql)
		{
			string filePath = Path.GetTempFileName();
			File.WriteAllText(filePath, sql);
			ScriptStep.RunSQLScriptFileWithOSQL(server, dbName, filePath);
			File.Delete(filePath);
		}

		private string getLogFilesDefinition(string dbName, XmlNode xmlEnvironment)
		{
			bool FilesFound = false;
			string s = "";
			int i = 0;
			string FilePhysicalName, FileLogicalName;
			//if the log files are defined for the current environment - use them, otherwise - skip
			if (xmlEnvironment != null)
			{
				//Get through the list of log files and build each of them or build default definition
				//if the filegroup configuration exists - get the filegroup creation statement from there.
				//But check if there is any file actually declared.
				foreach (XmlNode FileNode in xmlEnvironment.SelectNodes("Log/File"))
				{
					FilesFound = true;
					i = i + 1;
					FileLogicalName = FileNode.Attributes["LogicalName"].Value;
					if (FileLogicalName == "")
					{
						FileLogicalName = "log_" + i;
					}
					FilePhysicalName = FileNode.Attributes["PhysicalName"].Value;
					if (FilePhysicalName == "")
					{
						FilePhysicalName = DataFolderName + "\\" + dbName + "_log_" + i + ".ldf";
					}
					string InitialSize = FileNode.Attributes["InitialSize"].Value;
					if (InitialSize != "")
					{
						InitialSize = ", size=" + InitialSize;
					}
					string Growth = FileNode.Attributes["Growth"].Value;
					if (Growth != "")
					{
						Growth = ", filegrowth=" + Growth;
					}
					string MaxSize = FileNode.Attributes["MaxSize"].Value;
					if (MaxSize != "")
					{
						MaxSize = ", maxsize=" + MaxSize;
					}
					if (s != "")
					{
						s = s + ", ";
					}
					s = s + "(name=" + FileLogicalName + ", filename='" + FilePhysicalName + "'" + InitialSize + MaxSize + Growth +
						")\n";
				}
			}

			//if no files were found the filegroup is created over a single file named after the filegroup
			if (!FilesFound)
			{
				FileLogicalName = "log_1";
				FilePhysicalName = DataFolderName + "\\" + dbName + "_log_" + i + ".ldf";
				s = s + "(name=" + FileLogicalName + ", filename='" + FilePhysicalName + "')";
			}

			return s;
		}

		private string getFilegroupsDefinition(string dbName, XmlDocument xmlFileLayout, XmlNode xmlEnvironment)
		{
			string s = "";

			//Get through the list of filegroups and build each of them
			foreach (XmlNode fgNode in xmlFileLayout.SelectNodes("//Database/Filegroups/Filegroup"))
			{
				string FileGroupName = fgNode.Attributes["Name"].Value;
				if (s != "")
				{
					s = s + ", \n";
					if (FileGroupName != "primary")
					{
						s = s + "filegroup ";
					}
				}
				s = s + FileGroupName + GetFilegroupDefinition(xmlEnvironment, FileGroupName, dbName);
			}
			return s;
		}

		private string GetFilegroupDefinition(XmlNode xmlEnvironment, string fileGroupName, string Database_Name)
		{
			string s = "";
			int i = 0;
			bool FilesFound = false;
			string FilePhysicalName, FileLogicalName;

			//if there is a filegroup definition in the configuration file - use it, otherwise - skip.
			if (xmlEnvironment != null)
			{
				XmlNode xmlFilegroupConfig = null;
				//Get file group configuration node (if any)
				Logger.WriteTrace
					("Trying to identify if filegroup '" + fileGroupName +
						"' has specifically defined files in the config files for the server '" + SQLServerInstanceName +
							"' in environment '" + xmlEnvironment.Attributes["Name"].Value + "'");
				foreach (XmlNode fgNode in xmlEnvironment.SelectNodes("Filegroup"))
				{
					Logger.WriteTrace
						("In environment '" + xmlEnvironment.Attributes["Name"].Value + "' there is a filegroup named '" +
							fgNode.Attributes["Name"].Value + "'...");
					if (fgNode.Attributes["Name"].Value.ToUpper() == fileGroupName.ToUpper())
					{
						xmlFilegroupConfig = fgNode;
						Logger.WriteTrace
							("Found definition of filegroup '" + fileGroupName + "' in environment '" +
								xmlEnvironment.Attributes["Name"].Value +
									"' definition. if files are specified in there - there will be created appropriately.");
						break;
					}
				}
				if (xmlFilegroupConfig == null)
				{
					Logger.WriteTrace
						("Could not find definition of filegroup '" + fileGroupName + "' in environment '" +
							xmlEnvironment.Attributes["Name"].Value +
								"' definition. Filegroup will be created over a single file with the server defaults.");
				}
				else
				{
					//if the filegroup configuration exists - get the filegroup creation statement from there.
					//But check if there is any file actually declared.
					foreach (XmlNode fileNode in xmlFilegroupConfig.SelectNodes("File"))
					{
						FilesFound = true;
						i = i + 1;
						FileLogicalName = fileNode.Attributes["LogicalName"].Value;
						if (FileLogicalName == "")
						{
							FileLogicalName = Database_Name + "_" + fileGroupName + "_" + i;
							Logger.WriteTrace
								("There is no logical file name defined for logical file #" + i + " in filegroup '" + fileGroupName +
									"', default name is composed: '" + FileLogicalName + "'");
						}
						FilePhysicalName = fileNode.Attributes["PhysicalName"].Value;
						if (FilePhysicalName == "")
						{
							FilePhysicalName = DataFolderName + "\\" + Database_Name + "_" + fileGroupName + i;
							Logger.WriteTrace
								("There is no physical file name defined for logical file '" + FileLogicalName +
									"', default name is composed: '" + FilePhysicalName + "'");
						}
						string InitialSize = fileNode.Attributes["InitialSize"].Value;
						if (InitialSize != "")
						{
							InitialSize = ", size=" + InitialSize;
						}
						else
						{
							Logger.WriteTrace
								("There is no initial size specified for logical file '" + FileLogicalName +
									"', no value will be passed to the server, so it will use default value.");
						}
						string Growth = fileNode.Attributes["Growth"].Value;
						if (Growth != "")
						{
							Growth = ", filegrowth=" + Growth;
						}
						else
						{
							Logger.WriteTrace
								("There is no initial size specified for logical file '" + FileLogicalName +
									"', no value will be passed to the server, so it will use default value.");
						}
						string MaxSize = fileNode.Attributes["MaxSize"].Value;
						if (MaxSize != "")
						{
							MaxSize = ", maxsize=" + MaxSize;
						}
						else
						{
							Logger.WriteTrace
								("There is no maximum file size specified for logical file '" + FileLogicalName +
									"', no value will be passed to the server, so it will use default value.");
						}
						if (s != "")
						{
							s += ", \n";
						}
						s += "(name=" + FileLogicalName + ", filename='" + FilePhysicalName + "'" + InitialSize + MaxSize + Growth + ")";
					}
				}
			}

			//if no files were found the filegroup is created over a single file named after the filegroup
			if (!FilesFound)
			{
				Logger.WriteTrace
					("No filegroup definition was found - filegroup '" + fileGroupName +
						"' is created by default over a single file with the server defaults.");
				FileLogicalName = fileGroupName + "_1";
				FilePhysicalName = DataFolderName + "\\" + Database_Name + "_" + fileGroupName;
				if (fileGroupName == "primary")
				{
					FilePhysicalName = FilePhysicalName + ".mdf";
				}
				else
				{
					FilePhysicalName = FilePhysicalName + ".ndf";
				}
				s = s + "(Name=" + FileLogicalName + ",Filename='" + FilePhysicalName + "')";
			}
			return s;
		}

		private bool ADatabaseFromTheListExists()
		{
			Logger.WriteTrace("Checking existence of the databases which the script is supposed to create/operate");

			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				string dbOriginalName = dbNode.Attributes["Name"].Value;
				string dbMappedName = databaseNameMapping[dbOriginalName];
				Logger.WriteTrace
					("Checking for existence of the database '" + dbOriginalName + "' from configuration file under a name '" +
						dbMappedName + "'");
				if (DatabaseExists(dbMappedName))
				{
					return true;
				}
			}
			return false;
		}

		private bool DatabaseExists(string name)
		{
			Logger.WriteTrace("Checking if database " + name + " exists");
			using (
				SqlConnection connection = new SqlConnection
					("Server=" + SQLServerInstanceName + ";Database=master;Integrated Security=SSPI;"))
			{
				connection.Open();

				SqlCommand command = new SqlCommand("select * from sysdatabases where name = '" + name + "'", connection);

				SqlDataReader objRecordset = command.ExecuteReader();
				if (objRecordset != null && objRecordset.Read())
				{
					Logger.WriteTrace("Database " + name + " already exists");
					return true;
				}
				else
				{
					Logger.WriteTrace("Database " + name + " does not exist");
					return false;
				}
			}
		}

		private void GetDataAndLogFolders()
		{
			using (
				SqlConnection connection = new SqlConnection
					("Server=" + SQLServerInstanceName + ";Database=master;Integrated Security=SSPI;"))
			{
				connection.Open();

				SqlCommand command = new SqlCommand
					(@"declare @SmoDefaultFile nvarchar(512)
				exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
				declare @SmoDefaultLog nvarchar(512)
				exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT
				select @SmoDefaultFile as DataFolder,  @SmoDefaultLog as LogFolder",
						connection);

				SqlDataReader objRecordset = command.ExecuteReader();

				if (objRecordset != null && objRecordset.Read())
				{
					DataFolderName = objRecordset["DataFolder"] is string ? (string) objRecordset["DataFolder"] : null;
				}
				else
				{
					Logger.WriteTrace("Could not retrieve default data file folder name from target SQL Server instance");
					Environment.Exit(1);
				}
			}
		}

		private static string GetSQLServerInstanceName(string[] args)
		{
			Logger.WriteTrace("Checking script input parameters to get sql server name");
			string s = GetNamedParameter(args, "sqlservername");

			if (string.IsNullOrEmpty(s))
			{
				Logger.WriteTrace("SQL Server name was neither passed to the scipt nor given by user. Script is aborted.");
				Environment.Exit(1);
			}
			else
			{
				Logger.WriteTrace(string.Format("Server name was passed as a script input parameter with value '{0}'", s));
			}

			// Server name is re-confirmed from SQL Server - to convert (local) or "." into a real instance name
			// It's needed to map it to configuration file.

			Logger.WriteTrace(string.Format("Checking if SQL Server instance '{0}' exists and is accessible from the script", s));
			using (SqlConnection connection = new SqlConnection())
			{
				connection.ConnectionString = string.Format("Server={0};Database=master;Integrated Security=SSPI;", s);
				connection.Open();
				SqlCommand command = new SqlCommand("select @@servername", connection);

				s = (string) command.ExecuteScalar();
				Logger.WriteTrace("SQL Server instance name is identified as '" + s + "'");
			}

			return s;
		}

		private void LoadDatabaseNameMapping(string[] args)
		{
			// define the database mapping name file name from command line (not asking user if there is nothing - there will be defaults used)
			Logger.WriteTrace("Checking script input parameters to get database names mapping file");
			string s = GetNamedParameter(args, "dbnamemapping");
			if (string.IsNullOrEmpty(s))
			{
				Logger.WriteTrace("Database name mapping file name was not passed - default database names will be used");
			}
			else
			{
				Logger.WriteTrace
					(string.Format
						("Database name mapping file name was passed as a script input parameter 'dbnamemapping' with value '{0}'", s));
			}

			//if file name is passed - try to load it into a hash table
			Dictionary<string, string> nameMappingFromXML = new Dictionary<string, string>();
			if (!string.IsNullOrEmpty(s))
			{
				string cfgFileName = Path.Combine(script_execution_folder, s);

				// load the XML from file
				Logger.WriteTrace("Trying to locate database name mapping file: " + cfgFileName);
				if (!File.Exists(cfgFileName))
				{
					Logger.WriteTrace("ERROR: Database name mapping  file " + cfgFileName + " could not be found/open.");
					Logger.WriteTrace("ERROR: Script execution is aborted.");
					Environment.Exit(1);
				}

				XmlDocument mappingXML = new XmlDocument();

				Logger.WriteTrace("Loading configuration file: " + cfgFileName);
				//Reading an XML file with configuration
				mappingXML.Load(cfgFileName);
				Logger.WriteTrace("Database names mapping file loaded:" + cfgFileName);
				Logger.WriteTrace(mappingXML.InnerXml);

				//traverse the file and load values into hash table
				Logger.WriteTrace("Traversing database name mapping file");
				foreach (XmlNode dbNode in mappingXML.SelectNodes("//LogicalEnvironment/Database"))
				{
					nameMappingFromXML.Add(dbNode.Attributes["NameInConfig"].Value, dbNode.Attributes["NameOnServer"].Value);
				}
			}

			//initialize database name mapping hashtable:
			//for each database in environment configuration file find 
			databaseNameMapping = new Dictionary<string, string>();
			foreach (XmlNode dbNode in environmentConfigXML.SelectNodes("//System/Databases/Database"))
			{
				// if mapping is given - use it
				if (nameMappingFromXML.Keys.Contains(dbNode.Attributes["Name"].Value))
				{
					databaseNameMapping.Add(dbNode.Attributes["Name"].Value, nameMappingFromXML[dbNode.Attributes["Name"].Value]);
					Logger.WriteTrace
						("Database '" + dbNode.Attributes["Name"].Value + "' will be mapped to '" +
							nameMappingFromXML[dbNode.Attributes["Name"].Value] + "''- mapping was provided");
					// otherwise - use orginal database name
				}
				else
				{
					databaseNameMapping.Add(dbNode.Attributes["Name"].Value, dbNode.Attributes["Name"].Value);
					Logger.WriteTrace
						("Database '" + dbNode.Attributes["Name"].Value +
							"' will be created/processed with default name - no mapping was provided");
				}
			}
		}

		private static Operation DetectRequestedOperation(string[] args)
		{
			Logger.WriteTrace("Checking script input parameters to find requested operation");
			if (args.Length < 1)
			{
				Logger.WriteTrace
					("The script expected a command as a first parameter (create|updateStructure|updateCode|delete|getfullbuildscripts|createroles|createusers) which was not supplied. Script is aborted.");
				Environment.Exit(1);
			}

			return (Operation)Enum.Parse(typeof (Operation), args[0], true);
		}

		private void LoadEnvironmentConfig(string[] args)
		{
			// define the config file name/no defaults from command line, asking user if there is nothing 
			Logger.WriteTrace("Checking script input parameters to get environment configuration file");
			string s = GetNamedParameter(args, "envconfig");
			Logger.WriteTrace
				(string.Format
					("Environment configuration file name was passed as a script input parameter 'envconfig' with value '{0}'", s));

			if (string.IsNullOrEmpty(s))
			{
				Logger.WriteTrace("Environment configuration file name wasn't passed to the scipt. Script is aborted.");
				Environment.Exit(1);
			}

			ENVIRONMENT_CONFIG_FILE_NAME = Path.Combine(script_execution_folder, s);

			// load the XML from file
			Logger.WriteTrace("Trying to locate configuration file: " + ENVIRONMENT_CONFIG_FILE_NAME);
			if (!File.Exists(ENVIRONMENT_CONFIG_FILE_NAME))
			{
				Logger.WriteTrace("ERROR: Configuration file " + ENVIRONMENT_CONFIG_FILE_NAME + " could not be found/open.");
				Logger.WriteTrace("ERROR: Script execution is interrupted.");
				Environment.Exit(1);
			}

			environmentConfigXML = new XmlDocument();

			Logger.WriteTrace("Loading configuration file: " + ENVIRONMENT_CONFIG_FILE_NAME);
			environmentConfigXML.Load(ENVIRONMENT_CONFIG_FILE_NAME);

			DatabaseEnvironment = new DatabaseEnvironment(ENVIRONMENT_CONFIG_FILE_NAME);

			Logger.WriteTrace("Configuration file loaded:" + ENVIRONMENT_CONFIG_FILE_NAME);
			Logger.WriteTrace(environmentConfigXML.InnerXml);

			config_root_folder = Path.GetDirectoryName(ENVIRONMENT_CONFIG_FILE_NAME);
			Logger.WriteTrace("Database environment files are located in " + config_root_folder);
		}

		private static string GetNamedParameter(string[] args, string parameterName)
		{
			foreach (string arg in args)
			{
				if (arg.StartsWith("/" + parameterName + ":"))
				{
					return arg.Substring(parameterName.Length + 2);
				}
			}
			return null;
		}
	}
}